#!/usr/bin/python

import sys

try:
    import sqlite3 as sqlite
except ImportError:
    try:
        from pysqlite2 import dbapi2 as sqlite
    except ImportError:
        raise ImportError, 'This program requires the sqlite extension'

class NbDb:

    def __init__(self,sqlite_file):
        self.debug = ''
        self.debug_msg('NbDb.__init__: begin')
        self.db=sqlite_file
        self.con = sqlite.connect(sqlite_file)
        self.cur = self.con.cursor()
        self.debug_msg('NbDb.__init__: end')
        self.tb={}

    def debug_msg(self,msg):
        if (self.debug=='true'):
            print msg

    def create_table(self):
        self.debug_msg('NbDb.create_table: begin')
        req="CREATE TABLE "+self.tb['name']+" ("
        for field in self.tb['fields']:
            req+=field+', '
        req=req[:-2]
        req+=')'
        print req
        try:
            self.cur.execute(req)
            self.con.commit()
        except sqlite.OperationalError, e: 
            print 'Error while creating database'
            print e
            print req
        self.debug_msg('NbDb.create_table: end')
    
    def verify_table_fields(self):
        self.debug_msg('NbDb.verify_table_fields: begin')
        # TODO: perhaps use something like SQLObject, SQLAlchemy, to be able
        # alter table existing tables ..
        self.debug_msg('NbDb.verify_table_fields: end')
        pass
        
    def verify_table(self):
        self.debug_msg('NbDb.verify_table: begin')
        # Check if table exist, but use a method not depending of SQLite
        self.con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
        req="SELECT * FROM "+self.tb['name']+" LIMIT 1"
        try:
            self.cur.execute(req)
            #self.verify_table_fields()
        except sqlite.OperationalError, e:
            print str(e)
            print str(e).find("no such table")
            if (str(e).find("no such table")>=0):
                # TODO: only do that if table doesn't exist
                self.create_table()
            else:
                print e
                return
        self.debug_msg('NbDb.verify_table: end')

    def select_all(self):
        self.debug_msg('NbDb.select_all: begin')
        self.con.row_factory = sqlite.Row
        
        req="SELECT * FROM "+self.tb['name']
        self.cur.execute(req)
        self.debug_msg('NbDb.select_all: end')
        return self.cur.fetchall()

    def select_all_key_name(self):
        self.debug_msg('NbDb.select_all_key_name: begin')
        req="SELECT * FROM "+self.tb['name']
        self.cur.execute(req)
        list={}
        for row in self.cur.fetchall(): 
            list[row[1]]=row
        self.debug_msg('NbDb.select_all_key_name: end')
        return list

    def select_all_for_autocomplete(self,field):
        self.debug_msg('NbDb.select_all_for_autocomplete: begin')
        req="SELECT * FROM "+self.tb['name']+' ORDER BY '+field
        self.cur.execute(req)
        list=[]
        for row in self.cur.fetchall():
            list.append(row[1])
        self.debug_msg('NbDb.select_all_for_autocomplete: end')
        return list

    def add(self,fields):
       
        list_fields='id,'
        list_values=''
        for k,v in fields.iteritems():
            list_fields+=k+','
            list_values+="'%s'," % v
        
        # Remove last ","
        list_fields=list_fields[:-1]
        list_values=list_values[:-1]
        req='INSERT INTO ng_account ('+list_fields+') VALUES (NULL,'+list_values+')'

        self.cur.execute(req)
        self.con.commit()
        self.cur=self.con.cursor()
                        
        #print fields

class NgList(NbDb):
    
    def __init__(self,sqlite_file):

        self.debug=''
        self.debug_msg("NgList.__init__: begin")

        NbDb.__init__(self,sqlite_file)

        self.tb={}
        self.tb['name']='ng_list'
        self.tb['fields']=[]
        self.tb['fields'].append('id INTEGER PRIMARY KEY AUTOINCREMENT')
        self.tb['fields'].append('ng_name CHAR(255) UNIQUE')
        self.tb['fields'].append('ng_high CHAR(10)')
        self.tb['fields'].append('ng_low CHAR(10)')
        self.tb['fields'].append('ng_flag CHAR(1)')
        self.tb['fields'].append('last_nb_msg_updated INTEGER')

        self.verify_table()

        self.debug_msg('NgList.__init__: end')

    def add(self,name,high,low,flag,nb_inserted_message):
        req="INSERT INTO ng_list (id,ng_name,ng_high,ng_low,ng_flag,last_nb_msg_updated) "
        req+="VALUES "
        req+="(NULL,'%s','%s','%s','%s',%s);" % (name,high,low,flag,nb_inserted_message)
        self.cur.execute(req)

    def commit(self):
        self.con.commit()

    def edit(self,name,high,low,last_nb_msg_updated):
        req="UPDATE ng_list SET "
        req+="ng_high='%s', " % high
        req+="ng_low='%s', " % low
        req+="last_nb_msg_updated=%s " % last_nb_msg_updated
        req+="WHERE ng_name='%s'" % name
        self.cur.execute(req)

class NgGroup(NbDb):

    def __init__(self,sqlite_file,fields):

        self.debug=''
        self.debug_msg('NgGroup.__init__: begin')
        
        NbDb.__init__(self,sqlite_file)

        self.tb={}
        self.tb['name']='articles'
        self.tb['fields']=[]
        self.tb['fields'].append('id CHAR(255) UNIQUE')
                
        for field in fields:
            field=field.lower()
            field=field.replace('-','')
            if field=='from': field='afrom'
            if field=='references': field='areferences'
            self.tb['fields'].append(field+' CHAR(255)')

        self.verify_table()

        self.debug_msg('NgGroup.__init__: end')

    def update(self,items_list):

        self.debug=''
        self.debug_msg('NgGroup.update: begin')
        qmark='?,'*len(self.tb['fields'])
        qmark=qmark[:-1]
        for items in items_list:
            item_list=items.split("\t")
            real_item=[]
            for item in item_list:
                real_item.append(item)
            req="INSERT INTO articles VALUES ("+qmark+")"
            try:
                self.cur.execute(req, real_item)
            except sqlite.OperationalError, e:
                print 'Error while inserting article'
                print e
                print req
                return
            except sqlite.IntegrityError:
                continue 
                                                

        self.con.commit()

        self.debug_msg('NgGroup.update: end')

class NgListSubscribed(NbDb):

    def __init__(self,sqlite_file):

        self.debug=''
        self.debug_msg("NgList.__init__: begin")

        NbDb.__init__(self,sqlite_file)

        self.tb={}
        self.tb['name']='ng_listsubscribed'
        self.tb['fields']=[]
        self.tb['fields'].append('id INTEGER PRIMARY KEY AUTOINCREMENT')
        self.tb['fields'].append('ng_name CHAR(255) UNIQUE')
        self.tb['fields'].append('last_end CHAR(255)')

        self.verify_table()

        self.debug_msg('NgList.__init__: end')

    def add(self,name,end):
        req="INSERT INTO ng_listsubscribed (id,ng_name,last_end) "
        req+="VALUES "
        req+="(NULL,'%s','%s');" % (name,end)
        self.cur.execute(req)

    def commit(self):
        self.con.commit()


class NgAccount(NbDb):

    def __init__(self,sqlite_file):

        self.debug=''
        self.debug_msg('NgAccount.__init__: begin')

        NbDb.__init__(self,sqlite_file)
        
        self.tb={}
        self.tb['name']='ng_account'
        self.tb['fields']=[]
        self.tb['fields'].append('id INTEGER PRIMARY KEY AUTOINCREMENT')
        self.tb['fields'].append('acc_name CHAR(255) UNIQUE')
        self.tb['fields'].append('acc_host CHAR(255)')
        self.tb['fields'].append('acc_login CHAR(255)')
        self.tb['fields'].append('acc_password CHAR(255)')
        self.tb['fields'].append('acc_port CHAR(5)')

        self.verify_table()

        self.debug_msg("NgAccount.__init__: end")

    def remove(self,name):
    
        self.debug=''
        self.debug_msg('NgAccount.remove: begin')
        
        # WARNING: possible SQL Injection here, how can i use "?" 
        # not directly in cur.execute function ??
        req="DELETE FROM ng_account WHERE acc_name='%s'" % name
        self.cur.execute(req)
        self.con.commit()

        self.debug_msg('NgAccount.remove: end')

    def select_once(self,name):

        self.debug=''
        self.debug_msg('NgAccount.select_once: begin')

        req="SELECT * FROM ng_account WHERE acc_name='%s'" %name
        self.cur.execute(req)
        self.con.commit()

        return self.cur.fetchone()
        


